package com.AEWMS.Service;

import com.AEWMS.Dao.CourseScore;
import com.AEWMS.Pojo.AdminScoreView;
import com.AEWMS.Pojo.ScoreWarningRankView;
import com.AEWMS.Pojo.StuCourseScoreInfoView;
import com.AEWMS.Utlis.DbCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author lhs
 * @create 2021/2/22 16:16
 */
public class CourseScoreService {

    private Connection conn = null ;
    private PreparedStatement pstmt = null ;

    public CourseScoreService() {
        DbCon dbcon;
        try {
            dbcon = new DbCon();
            this.conn = dbcon.getConnection();
        } catch (Exception e) {
            System.out.println("CourseScoreService 连接数据库失败");
            e.printStackTrace();
        }
    }

    public void closeCon(){
        try {
            this.conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接关闭");
            e.printStackTrace();
        }
    }

    /**
     * 查找所有挂科课程成绩
     * @return 所有挂科成绩信息
     */
    public ArrayList<StuCourseScoreInfoView> findAllFailScore(String id){
        ArrayList<StuCourseScoreInfoView> allFailScoreList = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM stu_course_score_info_view where student_number = ? and course_score < course_standard";
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,id);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                StuCourseScoreInfoView s = new StuCourseScoreInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setCourseId(rs.getInt(8));
                s.setSemesterYear(rs.getString(9));
                s.setSemesterType(rs.getString(10));
                s.setCourseName(rs.getString(11));
                s.setCourseType(rs.getString(12));
                s.setCourseScore(rs.getString(13));
                s.setCourseStandard(rs.getInt(14));
                s.setCourseCredit(rs.getString(15));
                allFailScoreList.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("findAllCourseScore 失败");
            System.out.println(e);
        }
        return allFailScoreList;//输出包含所有课程成绩对象的数组
    }
    /**
     * 查找所有课程成绩
     * @return 所有课程成绩信息
     */
    public ArrayList<StuCourseScoreInfoView> findAllCourseScore(){
        ArrayList<StuCourseScoreInfoView> allCourseScoreList = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM stu_course_score_info_view";
        try {
            this.pstmt = conn.prepareStatement(sql);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                StuCourseScoreInfoView s = new StuCourseScoreInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setCourseId(rs.getInt(8));
                s.setSemesterYear(rs.getString(9));
                s.setSemesterType(rs.getString(10));
                s.setCourseName(rs.getString(11));
                s.setCourseType(rs.getString(12));
                s.setCourseScore(rs.getString(13));
                s.setCourseStandard(rs.getInt(14));
                s.setCourseCredit(rs.getString(15));
                allCourseScoreList.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("findAllCourseScore 失败");
            System.out.println(e);
        }
        return allCourseScoreList;//输出包含所有课程成绩对象的数组
    }
    /**
     * 查找所有课程成绩
     * @return 所有课程成绩信息
     */
    public ArrayList<StuCourseScoreInfoView> findAllCourseScoreBy(String id,String year,String type){
        ArrayList<StuCourseScoreInfoView> allCourseScoreList = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM stu_course_score_info_view";
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(3,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                StuCourseScoreInfoView s = new StuCourseScoreInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setCourseId(rs.getInt(8));
                s.setSemesterYear(rs.getString(9));
                s.setSemesterType(rs.getString(10));
                s.setCourseName(rs.getString(11));
                s.setCourseType(rs.getString(12));
                s.setCourseScore(rs.getString(13));
                s.setCourseStandard(rs.getInt(14));
                s.setCourseCredit(rs.getString(15));
                allCourseScoreList.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("findAllCourseScoreBy 失败");
            System.out.println(e);
        }
        return allCourseScoreList;//输出包含所有课程成绩对象的数组
    }
    /**
     * 学生查找所有学生预警成绩
     * @return 所有课程成绩信息
     */
    public ArrayList<ScoreWarningRankView> findAllStuScoreWarning(String id,String year,String type){
        ArrayList<ScoreWarningRankView> StuScoreWarning = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM score_warning_rank_view WHERE student_number = ? AND semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(3,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                ScoreWarningRankView s = new ScoreWarningRankView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setCourseType(rs.getString(10));
                s.setFailExams(rs.getLong(11));
                StuScoreWarning.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("StuScoreWarning 失败");
            System.out.println(e);
        }
        return StuScoreWarning;//输出包含所有课程成绩对象的数组
    }

    public ArrayList<AdminScoreView> findAllStudentScoreRankByyt(String year,String type){
        ArrayList<AdminScoreView> StuScoreWarning = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM admin_score_view WHERE  semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                AdminScoreView s = new AdminScoreView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setBxFail(rs.getDouble(10));
                s.setXxFail(rs.getDouble(11));
                StuScoreWarning.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("StuScoreWarning 失败");
            System.out.println(e);
        }
        return StuScoreWarning;//输出包含所有课程成绩对象的数组
    }

    /**
     * 管理员查找所有学生预警成绩
     * @return 所有课程成绩信息
     */
    public ArrayList<ScoreWarningRankView> findAllStuScoreWarningByyt(String year,String type){
        ArrayList<ScoreWarningRankView> StuScoreWarning = new ArrayList<>();//升级版数组，返回所有课程成绩对象
        String sql = "SELECT * FROM score_warning_rank_view WHERE  semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                ScoreWarningRankView s = new ScoreWarningRankView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setCourseType(rs.getString(10));
                s.setFailExams(rs.getLong(11));
                s.setIsDown(rs.getString(12));
                StuScoreWarning.add(s);//将查找到的行（每个课程成绩对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("StuScoreWarning 失败");
            System.out.println(e);
        }
        return StuScoreWarning;//输出包含所有课程成绩对象的数组
    }

    /**
     * 查找课程成绩
     * @param StuID,CourseId 通过id
     * @return 返回一个课程成绩对象
     */
    public StuCourseScoreInfoView findCourseScoreById(String StuID,String CourseId){
        StuCourseScoreInfoView s = new StuCourseScoreInfoView();
        String sql = "SELECT * FROM stu_course_score_info_view WHERE student_number = ? AND course_id = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,StuID);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,CourseId);

            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setCourseId(rs.getInt(8));
                s.setSemesterYear(rs.getString(9));
                s.setSemesterType(rs.getString(10));
                s.setCourseName(rs.getString(11));
                s.setCourseType(rs.getString(12));
                s.setCourseScore(rs.getString(13));
                s.setCourseStandard(rs.getInt(14));
                s.setCourseCredit(rs.getString(15));
            }
        }
        catch (Exception e) {
            System.out.println("findCourseScoreById 失败");
            System.out.println(e);
        }
        return s;
    }

    /**
     * 添加课程成绩
     * @return 一个课程成绩对象
     */
    public boolean insertCourseScore(CourseScore s){
        boolean flag = false;
        String sql = "INSERT INTO course_score_info VALUES(?,?,?,?)";
        try {
            this.pstmt = conn.prepareStatement(sql);//sql语句已经在这个准备对象里，有问号就要用准备对象
            this.pstmt.setString(1,s.getStudentNumber());
            this.pstmt.setInt(2,s.getCourseId());

            this.pstmt.setString(5,s.getStudySemester());
            this.pstmt.setString(6,s.getCourseScore());


            int rs = this.pstmt.executeUpdate();//这里不用再传sql
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("insertCourseScore defeat");
            System.out.println(e);
        }
        return flag;
    }

    /**
     * 修改一个课程成绩
     * @param s 一个课程成绩对象
     * @return 是否成功
     */
    public boolean updateCourseScoreAll(CourseScore s) {
        boolean flag = false;//update的where字句后面用 AND，delete的where字句后面用 &&
//        String sql = "UPDATE course_score_info SET course_score = ? WHERE student_id = ? AND course_id = ? AND  study_semester = ? ";//这里的字段名要与数据库的一致
        String sql = "UPDATE course_score_info SET course_score = ? WHERE student_number = ? AND course_id = ? AND  study_semester = ? ";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,s.getCourseScore());
            this.pstmt.setString(2,s.getStudentNumber());
            this.pstmt.setInt(3,s.getCourseId());
            this.pstmt.setString(4,s.getStudySemester());
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        } catch (Exception e) {
            System.out.println("updateCourseScore 失败");
            System.out.println(e);
        }
        return flag;

    }
    /**
     * 修改一个课程成绩
     * @param stuNum,courseId,score
     * @return 是否成功
     */
    public boolean updateCourseScore(String stuNum,String courseId,String score) {
        boolean flag = false;
        //update的where字句后面用 AND，delete的where字句后面用 &&
        String sql = "UPDATE course_score_info SET course_score = ? WHERE student_number = ? AND course_id = ?";
        //这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,score);
            this.pstmt.setString(2,stuNum);
            this.pstmt.setString(3,courseId);
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        } catch (Exception e) {
            System.out.println("updateCourseScore 失败");
            System.out.println(e);
        }
        return flag;

    }

    /**
     * 删除一个课程成绩
     * @param StuId,courseId,studySemester 学生id,课程id,学期
     * @return 是否成功
     */
    public boolean deleteCourseScoreById(String StuId,String courseId,String studySemester){
        boolean flag = false;
        String sql = "DELETE FROM course_score_info WHERE student_number = ?&&course_id = ?&&study_semester = ?";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,StuId);//这里的名字与dao.admin传进来的一致
            this.pstmt.setString(2,courseId);//这里的名字与dao.admin传进来的一致
            this.pstmt.setString(3,studySemester);//这里的名字与dao.admin传进来的一致
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("deleteCourseScoreById 失败");
            System.out.println(e);
        }
        return flag;
    }

}





